{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Solutions\n",
    "\n",
    "## About the Data\n",
    "In this notebook, we will be working with 2 datasets:\n",
    "- 2018 stock data for Facebook, Apple, Amazon, Netflix, and Google (obtained using the [`stock_analysis` package](https://github.com/stefmolin/stock-analysis)) and earthquake data from the USGS API.\n",
    "- Earthquake data from September 18, 2018 - October 13, 2018 (obtained from the US Geological Survey (USGS) using the [USGS API](https://earthquake.usgs.gov/fdsnws/event/1/))\n",
    "\n",
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "quakes = pd.read_csv('../../ch_04/exercises/earthquakes.csv')\n",
    "faang = pd.read_csv('../../ch_04/exercises/faang.csv', index_col='date', parse_dates=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 1\n",
    "With the `exercises/earthquakes.csv` file, select all the earthquakes in Japan with a `magType` of `mb` and a magnitude of 4.9 or greater."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mag</th>\n",
       "      <th>magType</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1563</th>\n",
       "      <td>4.9</td>\n",
       "      <td>mb</td>\n",
       "      <td>293km ESE of Iwo Jima, Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2576</th>\n",
       "      <td>5.4</td>\n",
       "      <td>mb</td>\n",
       "      <td>37km E of Tomakomai, Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072</th>\n",
       "      <td>4.9</td>\n",
       "      <td>mb</td>\n",
       "      <td>15km ENE of Hasaki, Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3632</th>\n",
       "      <td>4.9</td>\n",
       "      <td>mb</td>\n",
       "      <td>53km ESE of Hitachi, Japan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      mag magType                         place\n",
       "1563  4.9      mb  293km ESE of Iwo Jima, Japan\n",
       "2576  5.4      mb    37km E of Tomakomai, Japan\n",
       "3072  4.9      mb     15km ENE of Hasaki, Japan\n",
       "3632  4.9      mb    53km ESE of Hitachi, Japan"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quakes.query(\n",
    "    \"parsed_place == 'Japan' and magType == 'mb' and mag >= 4.9\"\n",
    ")[['mag', 'magType', 'place']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 2\n",
    "Create bins for each full number of magnitude (for example, the first bin is 0-1, the second is 1-2, and so on) with `magType` of `ml` and count how many are in each bin."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 2]    3105\n",
       "(0, 1]    2207\n",
       "(2, 3]     862\n",
       "(3, 4]     122\n",
       "(4, 5]       2\n",
       "(5, 6]       1\n",
       "(8, 9]       0\n",
       "(7, 8]       0\n",
       "(6, 7]       0\n",
       "Name: mag_bin, dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quakes.query(\"magType == 'ml'\").assign(\n",
    "    mag_bin=lambda x: pd.cut(x.mag, np.arange(0, 10))\n",
    ").mag_bin.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 3\n",
    "Using the `exercises/faang.csv` file, group by the ticker and resample to monthly frequency. Aggregate the open and close prices with the mean, the high price with the max, the low price with the min, and the volume with the sum."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">AAPL</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>170.714690</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>161.5708</td>\n",
       "      <td>170.699271</td>\n",
       "      <td>659679440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>164.562753</td>\n",
       "      <td>177.9059</td>\n",
       "      <td>147.9865</td>\n",
       "      <td>164.921884</td>\n",
       "      <td>927894473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>172.421381</td>\n",
       "      <td>180.7477</td>\n",
       "      <td>162.4660</td>\n",
       "      <td>171.878919</td>\n",
       "      <td>713727447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>167.332895</td>\n",
       "      <td>176.2526</td>\n",
       "      <td>158.2207</td>\n",
       "      <td>167.286924</td>\n",
       "      <td>666360147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>182.635582</td>\n",
       "      <td>187.9311</td>\n",
       "      <td>162.7911</td>\n",
       "      <td>183.207418</td>\n",
       "      <td>620976206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>186.605843</td>\n",
       "      <td>192.0247</td>\n",
       "      <td>178.7056</td>\n",
       "      <td>186.508652</td>\n",
       "      <td>527624365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>188.065786</td>\n",
       "      <td>193.7650</td>\n",
       "      <td>181.3655</td>\n",
       "      <td>188.179724</td>\n",
       "      <td>393843881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>210.460287</td>\n",
       "      <td>227.1001</td>\n",
       "      <td>195.0999</td>\n",
       "      <td>211.477743</td>\n",
       "      <td>700318837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>220.611742</td>\n",
       "      <td>227.8939</td>\n",
       "      <td>213.6351</td>\n",
       "      <td>220.356353</td>\n",
       "      <td>678972040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>219.489426</td>\n",
       "      <td>231.6645</td>\n",
       "      <td>204.4963</td>\n",
       "      <td>219.137822</td>\n",
       "      <td>789748068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>190.828681</td>\n",
       "      <td>220.6405</td>\n",
       "      <td>169.5328</td>\n",
       "      <td>190.246652</td>\n",
       "      <td>961321947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>164.537405</td>\n",
       "      <td>184.1501</td>\n",
       "      <td>145.9639</td>\n",
       "      <td>163.564732</td>\n",
       "      <td>898917007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">AMZN</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>1301.377143</td>\n",
       "      <td>1472.5800</td>\n",
       "      <td>1170.5100</td>\n",
       "      <td>1309.010952</td>\n",
       "      <td>96371290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>1447.112632</td>\n",
       "      <td>1528.7000</td>\n",
       "      <td>1265.9300</td>\n",
       "      <td>1442.363158</td>\n",
       "      <td>137784020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>1542.160476</td>\n",
       "      <td>1617.5400</td>\n",
       "      <td>1365.2000</td>\n",
       "      <td>1540.367619</td>\n",
       "      <td>130400151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>1475.841905</td>\n",
       "      <td>1638.1000</td>\n",
       "      <td>1352.8800</td>\n",
       "      <td>1468.220476</td>\n",
       "      <td>129945743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>1590.474545</td>\n",
       "      <td>1635.0000</td>\n",
       "      <td>1546.0200</td>\n",
       "      <td>1594.903636</td>\n",
       "      <td>71615299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>1699.088571</td>\n",
       "      <td>1763.1000</td>\n",
       "      <td>1635.0900</td>\n",
       "      <td>1698.823810</td>\n",
       "      <td>85941510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>1786.305714</td>\n",
       "      <td>1880.0500</td>\n",
       "      <td>1678.0600</td>\n",
       "      <td>1784.649048</td>\n",
       "      <td>97629820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>1891.957826</td>\n",
       "      <td>2025.5700</td>\n",
       "      <td>1776.0200</td>\n",
       "      <td>1897.851304</td>\n",
       "      <td>96575676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>1969.239474</td>\n",
       "      <td>2050.5000</td>\n",
       "      <td>1865.0000</td>\n",
       "      <td>1966.077895</td>\n",
       "      <td>94445693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>1799.630870</td>\n",
       "      <td>2033.1900</td>\n",
       "      <td>1476.3600</td>\n",
       "      <td>1782.058261</td>\n",
       "      <td>183228552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>1622.323810</td>\n",
       "      <td>1784.0000</td>\n",
       "      <td>1420.0000</td>\n",
       "      <td>1625.483810</td>\n",
       "      <td>139290208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>1572.922105</td>\n",
       "      <td>1778.3400</td>\n",
       "      <td>1307.0000</td>\n",
       "      <td>1559.443158</td>\n",
       "      <td>154812304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">FB</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>184.364762</td>\n",
       "      <td>190.6600</td>\n",
       "      <td>175.8000</td>\n",
       "      <td>184.962857</td>\n",
       "      <td>495655736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>180.721579</td>\n",
       "      <td>195.3200</td>\n",
       "      <td>167.1800</td>\n",
       "      <td>180.269474</td>\n",
       "      <td>516621991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>173.449524</td>\n",
       "      <td>186.1000</td>\n",
       "      <td>149.0200</td>\n",
       "      <td>173.489524</td>\n",
       "      <td>996232472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>164.163557</td>\n",
       "      <td>177.1000</td>\n",
       "      <td>150.5100</td>\n",
       "      <td>163.810476</td>\n",
       "      <td>751130388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>181.910509</td>\n",
       "      <td>192.7200</td>\n",
       "      <td>170.2300</td>\n",
       "      <td>182.930000</td>\n",
       "      <td>401144183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>194.974067</td>\n",
       "      <td>203.5500</td>\n",
       "      <td>186.4300</td>\n",
       "      <td>195.267619</td>\n",
       "      <td>387265765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>199.332143</td>\n",
       "      <td>218.6200</td>\n",
       "      <td>166.5600</td>\n",
       "      <td>199.967143</td>\n",
       "      <td>652763259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>177.598443</td>\n",
       "      <td>188.3000</td>\n",
       "      <td>170.2700</td>\n",
       "      <td>177.491957</td>\n",
       "      <td>549016789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>164.232895</td>\n",
       "      <td>173.8900</td>\n",
       "      <td>158.8656</td>\n",
       "      <td>164.377368</td>\n",
       "      <td>500468912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>154.873261</td>\n",
       "      <td>165.8800</td>\n",
       "      <td>139.0300</td>\n",
       "      <td>154.187826</td>\n",
       "      <td>622446235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>141.762857</td>\n",
       "      <td>154.1300</td>\n",
       "      <td>126.8500</td>\n",
       "      <td>141.635714</td>\n",
       "      <td>518150415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>137.529474</td>\n",
       "      <td>147.1900</td>\n",
       "      <td>123.0200</td>\n",
       "      <td>137.161053</td>\n",
       "      <td>558786249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">GOOG</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>1127.200952</td>\n",
       "      <td>1186.8900</td>\n",
       "      <td>1045.2300</td>\n",
       "      <td>1130.770476</td>\n",
       "      <td>28738485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>1088.629474</td>\n",
       "      <td>1174.0000</td>\n",
       "      <td>992.5600</td>\n",
       "      <td>1088.206842</td>\n",
       "      <td>42384105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>1096.108095</td>\n",
       "      <td>1177.0500</td>\n",
       "      <td>980.6400</td>\n",
       "      <td>1091.490476</td>\n",
       "      <td>45430049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>1038.415238</td>\n",
       "      <td>1094.1600</td>\n",
       "      <td>990.3700</td>\n",
       "      <td>1035.696190</td>\n",
       "      <td>41773275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>1064.021364</td>\n",
       "      <td>1110.7500</td>\n",
       "      <td>1006.2900</td>\n",
       "      <td>1069.275909</td>\n",
       "      <td>31849196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>1136.396190</td>\n",
       "      <td>1186.2900</td>\n",
       "      <td>1096.0100</td>\n",
       "      <td>1137.626667</td>\n",
       "      <td>32103642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>1183.464286</td>\n",
       "      <td>1273.8900</td>\n",
       "      <td>1093.8000</td>\n",
       "      <td>1187.590476</td>\n",
       "      <td>31953386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>1226.156957</td>\n",
       "      <td>1256.5000</td>\n",
       "      <td>1188.2400</td>\n",
       "      <td>1225.671739</td>\n",
       "      <td>28820379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>1176.878421</td>\n",
       "      <td>1212.9900</td>\n",
       "      <td>1146.9100</td>\n",
       "      <td>1175.808947</td>\n",
       "      <td>28863199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>1116.082174</td>\n",
       "      <td>1209.9600</td>\n",
       "      <td>995.8300</td>\n",
       "      <td>1110.940435</td>\n",
       "      <td>48496167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>1054.971429</td>\n",
       "      <td>1095.5700</td>\n",
       "      <td>996.0200</td>\n",
       "      <td>1056.162381</td>\n",
       "      <td>36735570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>1042.620000</td>\n",
       "      <td>1124.6500</td>\n",
       "      <td>970.1100</td>\n",
       "      <td>1037.420526</td>\n",
       "      <td>40256461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">NFLX</th>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>231.269286</td>\n",
       "      <td>286.8100</td>\n",
       "      <td>195.4200</td>\n",
       "      <td>232.908095</td>\n",
       "      <td>238377533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28</th>\n",
       "      <td>270.873158</td>\n",
       "      <td>297.3600</td>\n",
       "      <td>236.1100</td>\n",
       "      <td>271.443684</td>\n",
       "      <td>184585819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31</th>\n",
       "      <td>312.712857</td>\n",
       "      <td>333.9800</td>\n",
       "      <td>275.9000</td>\n",
       "      <td>312.228095</td>\n",
       "      <td>263449491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>309.129529</td>\n",
       "      <td>338.8200</td>\n",
       "      <td>271.2239</td>\n",
       "      <td>307.466190</td>\n",
       "      <td>262064417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>329.779759</td>\n",
       "      <td>356.1000</td>\n",
       "      <td>305.7300</td>\n",
       "      <td>331.536818</td>\n",
       "      <td>142051114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>384.557595</td>\n",
       "      <td>423.2056</td>\n",
       "      <td>352.8200</td>\n",
       "      <td>384.133333</td>\n",
       "      <td>244032001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>380.969090</td>\n",
       "      <td>419.7700</td>\n",
       "      <td>328.0000</td>\n",
       "      <td>381.515238</td>\n",
       "      <td>305487432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>345.409591</td>\n",
       "      <td>376.8085</td>\n",
       "      <td>310.9280</td>\n",
       "      <td>346.257826</td>\n",
       "      <td>213144082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>363.326842</td>\n",
       "      <td>383.2000</td>\n",
       "      <td>335.8300</td>\n",
       "      <td>362.641579</td>\n",
       "      <td>170832156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>340.025348</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>271.2093</td>\n",
       "      <td>335.445652</td>\n",
       "      <td>363589920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>290.643333</td>\n",
       "      <td>332.0499</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>290.344762</td>\n",
       "      <td>257126498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>266.309474</td>\n",
       "      <td>298.7200</td>\n",
       "      <td>231.2300</td>\n",
       "      <td>265.302368</td>\n",
       "      <td>234304628</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          open       high        low        close     volume\n",
       "ticker date                                                                 \n",
       "AAPL   2018-01-31   170.714690   176.6782   161.5708   170.699271  659679440\n",
       "       2018-02-28   164.562753   177.9059   147.9865   164.921884  927894473\n",
       "       2018-03-31   172.421381   180.7477   162.4660   171.878919  713727447\n",
       "       2018-04-30   167.332895   176.2526   158.2207   167.286924  666360147\n",
       "       2018-05-31   182.635582   187.9311   162.7911   183.207418  620976206\n",
       "       2018-06-30   186.605843   192.0247   178.7056   186.508652  527624365\n",
       "       2018-07-31   188.065786   193.7650   181.3655   188.179724  393843881\n",
       "       2018-08-31   210.460287   227.1001   195.0999   211.477743  700318837\n",
       "       2018-09-30   220.611742   227.8939   213.6351   220.356353  678972040\n",
       "       2018-10-31   219.489426   231.6645   204.4963   219.137822  789748068\n",
       "       2018-11-30   190.828681   220.6405   169.5328   190.246652  961321947\n",
       "       2018-12-31   164.537405   184.1501   145.9639   163.564732  898917007\n",
       "AMZN   2018-01-31  1301.377143  1472.5800  1170.5100  1309.010952   96371290\n",
       "       2018-02-28  1447.112632  1528.7000  1265.9300  1442.363158  137784020\n",
       "       2018-03-31  1542.160476  1617.5400  1365.2000  1540.367619  130400151\n",
       "       2018-04-30  1475.841905  1638.1000  1352.8800  1468.220476  129945743\n",
       "       2018-05-31  1590.474545  1635.0000  1546.0200  1594.903636   71615299\n",
       "       2018-06-30  1699.088571  1763.1000  1635.0900  1698.823810   85941510\n",
       "       2018-07-31  1786.305714  1880.0500  1678.0600  1784.649048   97629820\n",
       "       2018-08-31  1891.957826  2025.5700  1776.0200  1897.851304   96575676\n",
       "       2018-09-30  1969.239474  2050.5000  1865.0000  1966.077895   94445693\n",
       "       2018-10-31  1799.630870  2033.1900  1476.3600  1782.058261  183228552\n",
       "       2018-11-30  1622.323810  1784.0000  1420.0000  1625.483810  139290208\n",
       "       2018-12-31  1572.922105  1778.3400  1307.0000  1559.443158  154812304\n",
       "FB     2018-01-31   184.364762   190.6600   175.8000   184.962857  495655736\n",
       "       2018-02-28   180.721579   195.3200   167.1800   180.269474  516621991\n",
       "       2018-03-31   173.449524   186.1000   149.0200   173.489524  996232472\n",
       "       2018-04-30   164.163557   177.1000   150.5100   163.810476  751130388\n",
       "       2018-05-31   181.910509   192.7200   170.2300   182.930000  401144183\n",
       "       2018-06-30   194.974067   203.5500   186.4300   195.267619  387265765\n",
       "       2018-07-31   199.332143   218.6200   166.5600   199.967143  652763259\n",
       "       2018-08-31   177.598443   188.3000   170.2700   177.491957  549016789\n",
       "       2018-09-30   164.232895   173.8900   158.8656   164.377368  500468912\n",
       "       2018-10-31   154.873261   165.8800   139.0300   154.187826  622446235\n",
       "       2018-11-30   141.762857   154.1300   126.8500   141.635714  518150415\n",
       "       2018-12-31   137.529474   147.1900   123.0200   137.161053  558786249\n",
       "GOOG   2018-01-31  1127.200952  1186.8900  1045.2300  1130.770476   28738485\n",
       "       2018-02-28  1088.629474  1174.0000   992.5600  1088.206842   42384105\n",
       "       2018-03-31  1096.108095  1177.0500   980.6400  1091.490476   45430049\n",
       "       2018-04-30  1038.415238  1094.1600   990.3700  1035.696190   41773275\n",
       "       2018-05-31  1064.021364  1110.7500  1006.2900  1069.275909   31849196\n",
       "       2018-06-30  1136.396190  1186.2900  1096.0100  1137.626667   32103642\n",
       "       2018-07-31  1183.464286  1273.8900  1093.8000  1187.590476   31953386\n",
       "       2018-08-31  1226.156957  1256.5000  1188.2400  1225.671739   28820379\n",
       "       2018-09-30  1176.878421  1212.9900  1146.9100  1175.808947   28863199\n",
       "       2018-10-31  1116.082174  1209.9600   995.8300  1110.940435   48496167\n",
       "       2018-11-30  1054.971429  1095.5700   996.0200  1056.162381   36735570\n",
       "       2018-12-31  1042.620000  1124.6500   970.1100  1037.420526   40256461\n",
       "NFLX   2018-01-31   231.269286   286.8100   195.4200   232.908095  238377533\n",
       "       2018-02-28   270.873158   297.3600   236.1100   271.443684  184585819\n",
       "       2018-03-31   312.712857   333.9800   275.9000   312.228095  263449491\n",
       "       2018-04-30   309.129529   338.8200   271.2239   307.466190  262064417\n",
       "       2018-05-31   329.779759   356.1000   305.7300   331.536818  142051114\n",
       "       2018-06-30   384.557595   423.2056   352.8200   384.133333  244032001\n",
       "       2018-07-31   380.969090   419.7700   328.0000   381.515238  305487432\n",
       "       2018-08-31   345.409591   376.8085   310.9280   346.257826  213144082\n",
       "       2018-09-30   363.326842   383.2000   335.8300   362.641579  170832156\n",
       "       2018-10-31   340.025348   386.7999   271.2093   335.445652  363589920\n",
       "       2018-11-30   290.643333   332.0499   250.0000   290.344762  257126498\n",
       "       2018-12-31   266.309474   298.7200   231.2300   265.302368  234304628"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang.groupby('ticker').resample('1M').agg(\n",
    "    {\n",
    "        'open' : np.mean,\n",
    "        'high' : np.max,\n",
    "        'low' : np.min,\n",
    "        'close' : np.mean,\n",
    "        'volume' : np.sum\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 4\n",
    "Build a crosstab with the earthquake data between the `tsunami` column and the `magType` column. Rather than showing the frequency count, show the maximum magnitude that was observed for each combination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>magType</th>\n",
       "      <th>mb</th>\n",
       "      <th>mb_lg</th>\n",
       "      <th>md</th>\n",
       "      <th>mh</th>\n",
       "      <th>ml</th>\n",
       "      <th>ms_20</th>\n",
       "      <th>mw</th>\n",
       "      <th>mwb</th>\n",
       "      <th>mwr</th>\n",
       "      <th>mww</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tsunami</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.6</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.11</td>\n",
       "      <td>1.1</td>\n",
       "      <td>4.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.83</td>\n",
       "      <td>5.8</td>\n",
       "      <td>4.8</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.1</td>\n",
       "      <td>5.7</td>\n",
       "      <td>4.41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "magType   mb  mb_lg    md   mh   ml  ms_20    mw  mwb  mwr  mww\n",
       "tsunami                                                        \n",
       "0        5.6    3.5  4.11  1.1  4.2    NaN  3.83  5.8  4.8  6.0\n",
       "1        6.1    NaN   NaN  NaN  5.1    5.7  4.41  NaN  NaN  7.5"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(quakes.tsunami, quakes.magType, values=quakes.mag, aggfunc='max')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 5\n",
    "Calculate the rolling 60-day aggregations of OHLC data by ticker for the FAANG data. Use the same aggregations as exercise 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"30\" valign=\"top\">AAPL</th>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>166.927100</td>\n",
       "      <td>169.0264</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>168.987200</td>\n",
       "      <td>2.555593e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>168.089600</td>\n",
       "      <td>171.2337</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>168.972500</td>\n",
       "      <td>5.507383e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>168.480367</td>\n",
       "      <td>171.2337</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>169.229200</td>\n",
       "      <td>7.750843e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>168.896475</td>\n",
       "      <td>172.0381</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>169.840675</td>\n",
       "      <td>1.011684e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>169.324680</td>\n",
       "      <td>172.2736</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>170.080040</td>\n",
       "      <td>1.217362e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-09</th>\n",
       "      <td>169.642850</td>\n",
       "      <td>172.2736</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>170.236350</td>\n",
       "      <td>1.433202e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-10</th>\n",
       "      <td>169.675314</td>\n",
       "      <td>172.2736</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>170.342386</td>\n",
       "      <td>1.672801e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-11</th>\n",
       "      <td>169.875012</td>\n",
       "      <td>172.2736</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>170.543312</td>\n",
       "      <td>1.859478e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-12</th>\n",
       "      <td>170.203644</td>\n",
       "      <td>173.9903</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>170.896878</td>\n",
       "      <td>2.113659e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-16</th>\n",
       "      <td>170.635280</td>\n",
       "      <td>175.9817</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>171.091440</td>\n",
       "      <td>2.409319e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-17</th>\n",
       "      <td>170.832373</td>\n",
       "      <td>175.9817</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>171.510145</td>\n",
       "      <td>2.753187e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-18</th>\n",
       "      <td>171.259850</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>171.872150</td>\n",
       "      <td>3.065120e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-19</th>\n",
       "      <td>171.564215</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>172.118092</td>\n",
       "      <td>3.389371e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-22</th>\n",
       "      <td>171.733300</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>172.226593</td>\n",
       "      <td>3.660457e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-23</th>\n",
       "      <td>171.879840</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>172.323247</td>\n",
       "      <td>3.987348e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-24</th>\n",
       "      <td>172.005000</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>172.234919</td>\n",
       "      <td>4.498399e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-25</th>\n",
       "      <td>171.957029</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>171.977512</td>\n",
       "      <td>4.913689e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-26</th>\n",
       "      <td>171.777867</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>166.0442</td>\n",
       "      <td>171.770506</td>\n",
       "      <td>5.305119e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-29</th>\n",
       "      <td>171.522563</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>163.8958</td>\n",
       "      <td>171.402000</td>\n",
       "      <td>5.811523e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-30</th>\n",
       "      <td>171.065445</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>161.5708</td>\n",
       "      <td>171.021785</td>\n",
       "      <td>6.272005e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>170.714690</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>161.5708</td>\n",
       "      <td>170.699271</td>\n",
       "      <td>6.596794e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>170.408977</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>161.5708</td>\n",
       "      <td>170.421682</td>\n",
       "      <td>7.069102e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>170.080157</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>157.0582</td>\n",
       "      <td>169.857722</td>\n",
       "      <td>7.935041e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-05</th>\n",
       "      <td>169.496700</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>153.0361</td>\n",
       "      <td>169.176850</td>\n",
       "      <td>8.662426e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-06</th>\n",
       "      <td>168.792368</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>151.0741</td>\n",
       "      <td>168.807080</td>\n",
       "      <td>9.344864e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-07</th>\n",
       "      <td>168.453681</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>151.0741</td>\n",
       "      <td>168.334073</td>\n",
       "      <td>9.860950e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-08</th>\n",
       "      <td>168.038530</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>151.0741</td>\n",
       "      <td>167.736600</td>\n",
       "      <td>1.040486e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-09</th>\n",
       "      <td>167.562657</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>147.9865</td>\n",
       "      <td>167.248293</td>\n",
       "      <td>1.111158e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-12</th>\n",
       "      <td>167.168172</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>147.9865</td>\n",
       "      <td>167.007645</td>\n",
       "      <td>1.171978e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-13</th>\n",
       "      <td>166.913263</td>\n",
       "      <td>176.6782</td>\n",
       "      <td>147.9865</td>\n",
       "      <td>166.836557</td>\n",
       "      <td>1.204527e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"30\" valign=\"top\">NFLX</th>\n",
       "      <th>2018-11-15</th>\n",
       "      <td>338.466659</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>271.2093</td>\n",
       "      <td>335.577955</td>\n",
       "      <td>5.923447e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-16</th>\n",
       "      <td>336.714841</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>271.2093</td>\n",
       "      <td>334.120227</td>\n",
       "      <td>5.943723e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-19</th>\n",
       "      <td>333.365786</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>269.1500</td>\n",
       "      <td>330.283810</td>\n",
       "      <td>5.783072e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-20</th>\n",
       "      <td>330.700071</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>328.040714</td>\n",
       "      <td>5.830704e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-21</th>\n",
       "      <td>329.391233</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>326.507907</td>\n",
       "      <td>5.940934e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-23</th>\n",
       "      <td>327.091465</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>323.931395</td>\n",
       "      <td>5.900160e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-26</th>\n",
       "      <td>321.994951</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>318.596585</td>\n",
       "      <td>5.745888e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-27</th>\n",
       "      <td>319.068122</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>315.974634</td>\n",
       "      <td>5.786234e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-28</th>\n",
       "      <td>317.946976</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>315.181190</td>\n",
       "      <td>5.934248e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-29</th>\n",
       "      <td>317.118442</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>314.566512</td>\n",
       "      <td>6.088563e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-11-30</th>\n",
       "      <td>315.075419</td>\n",
       "      <td>386.7999</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>312.350233</td>\n",
       "      <td>6.123399e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-03</th>\n",
       "      <td>309.820561</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>307.402927</td>\n",
       "      <td>6.029456e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-04</th>\n",
       "      <td>308.073244</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>305.548780</td>\n",
       "      <td>6.022232e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-06</th>\n",
       "      <td>307.126976</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>305.009048</td>\n",
       "      <td>6.152975e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-07</th>\n",
       "      <td>305.634119</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>303.010000</td>\n",
       "      <td>6.153887e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-10</th>\n",
       "      <td>301.847075</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>299.835500</td>\n",
       "      <td>5.829741e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-11</th>\n",
       "      <td>300.209825</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>297.979500</td>\n",
       "      <td>5.779465e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-12</th>\n",
       "      <td>299.415927</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>297.416098</td>\n",
       "      <td>5.894032e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-13</th>\n",
       "      <td>298.897452</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>296.906667</td>\n",
       "      <td>5.977825e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-14</th>\n",
       "      <td>297.330310</td>\n",
       "      <td>380.0000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>295.328333</td>\n",
       "      <td>5.964829e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-17</th>\n",
       "      <td>291.953500</td>\n",
       "      <td>355.8000</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>290.219500</td>\n",
       "      <td>5.348892e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-18</th>\n",
       "      <td>289.761000</td>\n",
       "      <td>336.5800</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>288.676250</td>\n",
       "      <td>5.285220e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-19</th>\n",
       "      <td>289.278049</td>\n",
       "      <td>336.5800</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>288.141951</td>\n",
       "      <td>5.423105e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-20</th>\n",
       "      <td>288.691429</td>\n",
       "      <td>336.5800</td>\n",
       "      <td>250.0000</td>\n",
       "      <td>287.485714</td>\n",
       "      <td>5.591034e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-21</th>\n",
       "      <td>287.042143</td>\n",
       "      <td>336.5800</td>\n",
       "      <td>241.2900</td>\n",
       "      <td>285.505952</td>\n",
       "      <td>5.634038e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>283.509250</td>\n",
       "      <td>332.0499</td>\n",
       "      <td>233.6800</td>\n",
       "      <td>281.931750</td>\n",
       "      <td>5.256579e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>281.844500</td>\n",
       "      <td>332.0499</td>\n",
       "      <td>231.2300</td>\n",
       "      <td>280.777750</td>\n",
       "      <td>5.204446e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>281.070488</td>\n",
       "      <td>332.0499</td>\n",
       "      <td>231.2300</td>\n",
       "      <td>280.162805</td>\n",
       "      <td>5.326798e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>279.916341</td>\n",
       "      <td>332.0499</td>\n",
       "      <td>231.2300</td>\n",
       "      <td>279.461341</td>\n",
       "      <td>5.219682e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>278.430769</td>\n",
       "      <td>332.0499</td>\n",
       "      <td>231.2300</td>\n",
       "      <td>277.451410</td>\n",
       "      <td>4.763097e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1255 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         open      high       low       close        volume\n",
       "ticker date                                                                \n",
       "AAPL   2018-01-02  166.927100  169.0264  166.0442  168.987200  2.555593e+07\n",
       "       2018-01-03  168.089600  171.2337  166.0442  168.972500  5.507383e+07\n",
       "       2018-01-04  168.480367  171.2337  166.0442  169.229200  7.750843e+07\n",
       "       2018-01-05  168.896475  172.0381  166.0442  169.840675  1.011684e+08\n",
       "       2018-01-08  169.324680  172.2736  166.0442  170.080040  1.217362e+08\n",
       "       2018-01-09  169.642850  172.2736  166.0442  170.236350  1.433202e+08\n",
       "       2018-01-10  169.675314  172.2736  166.0442  170.342386  1.672801e+08\n",
       "       2018-01-11  169.875012  172.2736  166.0442  170.543312  1.859478e+08\n",
       "       2018-01-12  170.203644  173.9903  166.0442  170.896878  2.113659e+08\n",
       "       2018-01-16  170.635280  175.9817  166.0442  171.091440  2.409319e+08\n",
       "       2018-01-17  170.832373  175.9817  166.0442  171.510145  2.753187e+08\n",
       "       2018-01-18  171.259850  176.6782  166.0442  171.872150  3.065120e+08\n",
       "       2018-01-19  171.564215  176.6782  166.0442  172.118092  3.389371e+08\n",
       "       2018-01-22  171.733300  176.6782  166.0442  172.226593  3.660457e+08\n",
       "       2018-01-23  171.879840  176.6782  166.0442  172.323247  3.987348e+08\n",
       "       2018-01-24  172.005000  176.6782  166.0442  172.234919  4.498399e+08\n",
       "       2018-01-25  171.957029  176.6782  166.0442  171.977512  4.913689e+08\n",
       "       2018-01-26  171.777867  176.6782  166.0442  171.770506  5.305119e+08\n",
       "       2018-01-29  171.522563  176.6782  163.8958  171.402000  5.811523e+08\n",
       "       2018-01-30  171.065445  176.6782  161.5708  171.021785  6.272005e+08\n",
       "       2018-01-31  170.714690  176.6782  161.5708  170.699271  6.596794e+08\n",
       "       2018-02-01  170.408977  176.6782  161.5708  170.421682  7.069102e+08\n",
       "       2018-02-02  170.080157  176.6782  157.0582  169.857722  7.935041e+08\n",
       "       2018-02-05  169.496700  176.6782  153.0361  169.176850  8.662426e+08\n",
       "       2018-02-06  168.792368  176.6782  151.0741  168.807080  9.344864e+08\n",
       "       2018-02-07  168.453681  176.6782  151.0741  168.334073  9.860950e+08\n",
       "       2018-02-08  168.038530  176.6782  151.0741  167.736600  1.040486e+09\n",
       "       2018-02-09  167.562657  176.6782  147.9865  167.248293  1.111158e+09\n",
       "       2018-02-12  167.168172  176.6782  147.9865  167.007645  1.171978e+09\n",
       "       2018-02-13  166.913263  176.6782  147.9865  166.836557  1.204527e+09\n",
       "...                       ...       ...       ...         ...           ...\n",
       "NFLX   2018-11-15  338.466659  386.7999  271.2093  335.577955  5.923447e+08\n",
       "       2018-11-16  336.714841  386.7999  271.2093  334.120227  5.943723e+08\n",
       "       2018-11-19  333.365786  386.7999  269.1500  330.283810  5.783072e+08\n",
       "       2018-11-20  330.700071  386.7999  250.0000  328.040714  5.830704e+08\n",
       "       2018-11-21  329.391233  386.7999  250.0000  326.507907  5.940934e+08\n",
       "       2018-11-23  327.091465  386.7999  250.0000  323.931395  5.900160e+08\n",
       "       2018-11-26  321.994951  386.7999  250.0000  318.596585  5.745888e+08\n",
       "       2018-11-27  319.068122  386.7999  250.0000  315.974634  5.786234e+08\n",
       "       2018-11-28  317.946976  386.7999  250.0000  315.181190  5.934248e+08\n",
       "       2018-11-29  317.118442  386.7999  250.0000  314.566512  6.088563e+08\n",
       "       2018-11-30  315.075419  386.7999  250.0000  312.350233  6.123399e+08\n",
       "       2018-12-03  309.820561  380.0000  250.0000  307.402927  6.029456e+08\n",
       "       2018-12-04  308.073244  380.0000  250.0000  305.548780  6.022232e+08\n",
       "       2018-12-06  307.126976  380.0000  250.0000  305.009048  6.152975e+08\n",
       "       2018-12-07  305.634119  380.0000  250.0000  303.010000  6.153887e+08\n",
       "       2018-12-10  301.847075  380.0000  250.0000  299.835500  5.829741e+08\n",
       "       2018-12-11  300.209825  380.0000  250.0000  297.979500  5.779465e+08\n",
       "       2018-12-12  299.415927  380.0000  250.0000  297.416098  5.894032e+08\n",
       "       2018-12-13  298.897452  380.0000  250.0000  296.906667  5.977825e+08\n",
       "       2018-12-14  297.330310  380.0000  250.0000  295.328333  5.964829e+08\n",
       "       2018-12-17  291.953500  355.8000  250.0000  290.219500  5.348892e+08\n",
       "       2018-12-18  289.761000  336.5800  250.0000  288.676250  5.285220e+08\n",
       "       2018-12-19  289.278049  336.5800  250.0000  288.141951  5.423105e+08\n",
       "       2018-12-20  288.691429  336.5800  250.0000  287.485714  5.591034e+08\n",
       "       2018-12-21  287.042143  336.5800  241.2900  285.505952  5.634038e+08\n",
       "       2018-12-24  283.509250  332.0499  233.6800  281.931750  5.256579e+08\n",
       "       2018-12-26  281.844500  332.0499  231.2300  280.777750  5.204446e+08\n",
       "       2018-12-27  281.070488  332.0499  231.2300  280.162805  5.326798e+08\n",
       "       2018-12-28  279.916341  332.0499  231.2300  279.461341  5.219682e+08\n",
       "       2018-12-31  278.430769  332.0499  231.2300  277.451410  4.763097e+08\n",
       "\n",
       "[1255 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang.groupby('ticker').rolling('60D').agg(\n",
    "    {\n",
    "        'open' : np.mean,\n",
    "        'high' : np.max,\n",
    "        'low' : np.min,\n",
    "        'close' : np.mean,\n",
    "        'volume' : np.sum\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 6\n",
    "Create a pivot table of the FAANG data that compares the stocks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>close</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>186.986218</td>\n",
       "      <td>188.906858</td>\n",
       "      <td>185.135729</td>\n",
       "      <td>187.038674</td>\n",
       "      <td>3.402145e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMZN</th>\n",
       "      <td>1641.726175</td>\n",
       "      <td>1662.839801</td>\n",
       "      <td>1619.840398</td>\n",
       "      <td>1644.072669</td>\n",
       "      <td>5.649563e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FB</th>\n",
       "      <td>171.510936</td>\n",
       "      <td>173.615298</td>\n",
       "      <td>169.303110</td>\n",
       "      <td>171.454424</td>\n",
       "      <td>2.768798e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOG</th>\n",
       "      <td>1113.225139</td>\n",
       "      <td>1125.777649</td>\n",
       "      <td>1101.001594</td>\n",
       "      <td>1113.554104</td>\n",
       "      <td>1.742645e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NFLX</th>\n",
       "      <td>319.290299</td>\n",
       "      <td>325.224583</td>\n",
       "      <td>313.187273</td>\n",
       "      <td>319.620533</td>\n",
       "      <td>1.147030e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              close         high          low         open        volume\n",
       "ticker                                                                  \n",
       "AAPL     186.986218   188.906858   185.135729   187.038674  3.402145e+07\n",
       "AMZN    1641.726175  1662.839801  1619.840398  1644.072669  5.649563e+06\n",
       "FB       171.510936   173.615298   169.303110   171.454424  2.768798e+07\n",
       "GOOG    1113.225139  1125.777649  1101.001594  1113.554104  1.742645e+06\n",
       "NFLX     319.290299   325.224583   313.187273   319.620533  1.147030e+07"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang.pivot_table(index='ticker')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 7\n",
    "Calculate the Z-scores of Netflix's data (ticker: NFLX)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>-2.500753</td>\n",
       "      <td>-2.516023</td>\n",
       "      <td>-2.410226</td>\n",
       "      <td>-2.416644</td>\n",
       "      <td>-0.088760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>-2.380291</td>\n",
       "      <td>-2.423180</td>\n",
       "      <td>-2.285793</td>\n",
       "      <td>-2.335286</td>\n",
       "      <td>-0.507606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>-2.296272</td>\n",
       "      <td>-2.406077</td>\n",
       "      <td>-2.234616</td>\n",
       "      <td>-2.323429</td>\n",
       "      <td>-0.959287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>-2.275014</td>\n",
       "      <td>-2.345607</td>\n",
       "      <td>-2.202087</td>\n",
       "      <td>-2.234303</td>\n",
       "      <td>-0.782331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>-2.218934</td>\n",
       "      <td>-2.295113</td>\n",
       "      <td>-2.143759</td>\n",
       "      <td>-2.192192</td>\n",
       "      <td>-1.038531</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                open      high       low     close    volume\n",
       "date                                                        \n",
       "2018-01-02 -2.500753 -2.516023 -2.410226 -2.416644 -0.088760\n",
       "2018-01-03 -2.380291 -2.423180 -2.285793 -2.335286 -0.507606\n",
       "2018-01-04 -2.296272 -2.406077 -2.234616 -2.323429 -0.959287\n",
       "2018-01-05 -2.275014 -2.345607 -2.202087 -2.234303 -0.782331\n",
       "2018-01-08 -2.218934 -2.295113 -2.143759 -2.192192 -1.038531"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang.query(\"ticker == 'NFLX'\").drop(columns='ticker').apply(\n",
    "    lambda x: x.sub(x.mean()).div(x.std())\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 8\n",
    "Adding event descriptions:\n",
    "1. Create a dataframe with three columns: ticker, date, and event.\n",
    "    1. ticker will be 'FB'.\n",
    "    2. date will be datetimes ['2018-07-25', '2018-03-19', '2018-03-20']\n",
    "    3. event will be ['Disappointing user growth announced after close.', 'Cambridge Analytica story', 'FTC investigation'].\n",
    "2. Merge this data to the FAANG data with a outer join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <th>AAPL</th>\n",
       "      <td>169.2521</td>\n",
       "      <td>171.2337</td>\n",
       "      <td>168.6929</td>\n",
       "      <td>168.9578</td>\n",
       "      <td>29517899</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-23</th>\n",
       "      <th>NFLX</th>\n",
       "      <td>329.0400</td>\n",
       "      <td>345.0000</td>\n",
       "      <td>328.0900</td>\n",
       "      <td>344.7200</td>\n",
       "      <td>10049147</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-17</th>\n",
       "      <th>FB</th>\n",
       "      <td>179.2600</td>\n",
       "      <td>179.3200</td>\n",
       "      <td>175.8000</td>\n",
       "      <td>177.6000</td>\n",
       "      <td>27992376</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-17</th>\n",
       "      <th>AMZN</th>\n",
       "      <td>1842.7900</td>\n",
       "      <td>1845.0000</td>\n",
       "      <td>1807.0000</td>\n",
       "      <td>1831.7300</td>\n",
       "      <td>5295177</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-26</th>\n",
       "      <th>AMZN</th>\n",
       "      <td>1509.2000</td>\n",
       "      <td>1522.8400</td>\n",
       "      <td>1507.0000</td>\n",
       "      <td>1521.9500</td>\n",
       "      <td>4954988</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <th>GOOG</th>\n",
       "      <td>1094.0000</td>\n",
       "      <td>1104.2500</td>\n",
       "      <td>1092.0000</td>\n",
       "      <td>1102.2300</td>\n",
       "      <td>1279123</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-04</th>\n",
       "      <th>FB</th>\n",
       "      <td>152.0250</td>\n",
       "      <td>155.5600</td>\n",
       "      <td>150.5100</td>\n",
       "      <td>155.1000</td>\n",
       "      <td>49885584</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-30</th>\n",
       "      <th>AMZN</th>\n",
       "      <td>1618.1000</td>\n",
       "      <td>1626.0000</td>\n",
       "      <td>1612.9300</td>\n",
       "      <td>1624.8900</td>\n",
       "      <td>2907357</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-17</th>\n",
       "      <th>NFLX</th>\n",
       "      <td>329.6600</td>\n",
       "      <td>338.6200</td>\n",
       "      <td>323.7700</td>\n",
       "      <td>336.0600</td>\n",
       "      <td>33866456</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-15</th>\n",
       "      <th>AMZN</th>\n",
       "      <td>1714.0000</td>\n",
       "      <td>1720.8700</td>\n",
       "      <td>1708.5200</td>\n",
       "      <td>1715.9700</td>\n",
       "      <td>4777646</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        open       high        low      close    volume event\n",
       "date       ticker                                                            \n",
       "2018-01-03 AAPL     169.2521   171.2337   168.6929   168.9578  29517899   NaN\n",
       "2018-05-23 NFLX     329.0400   345.0000   328.0900   344.7200  10049147   NaN\n",
       "2018-01-17 FB       179.2600   179.3200   175.8000   177.6000  27992376   NaN\n",
       "2018-10-17 AMZN    1842.7900  1845.0000  1807.0000  1831.7300   5295177   NaN\n",
       "2018-02-26 AMZN    1509.2000  1522.8400  1507.0000  1521.9500   4954988   NaN\n",
       "2018-01-05 GOOG    1094.0000  1104.2500  1092.0000  1102.2300   1279123   NaN\n",
       "2018-04-04 FB       152.0250   155.5600   150.5100   155.1000  49885584   NaN\n",
       "2018-05-30 AMZN    1618.1000  1626.0000  1612.9300  1624.8900   2907357   NaN\n",
       "2018-04-17 NFLX     329.6600   338.6200   323.7700   336.0600  33866456   NaN\n",
       "2018-06-15 AMZN    1714.0000  1720.8700  1708.5200  1715.9700   4777646   NaN"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events = pd.DataFrame({\n",
    "    'ticker' : 'FB',\n",
    "    'date' : pd.to_datetime(\n",
    "         ['2018-07-25', '2018-03-19', '2018-03-20']\n",
    "    ), 'event' : [\n",
    "         'Disappointing user growth announced after close.',\n",
    "         'Cambridge Analytica story',\n",
    "         'FTC investigation'\n",
    "    ]\n",
    "}).set_index(['date', 'ticker'])\n",
    "faang.reset_index().set_index(['date', 'ticker']).join(\n",
    "    events, how='outer'\n",
    ").sample(10, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 9\n",
    "Use the `transform()` method on the FAANG data, to represent all the values in terms of the first date in the data. To do so, divide all values by the values of the first date. This is referred to as an index and the first date is the base. [More information](https://ec.europa.eu/eurostat/statistics-explained/index.php/Beginners:Statistical_concept_-_Index_and_base_year)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">FB</th>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>1.023638</td>\n",
       "      <td>1.017623</td>\n",
       "      <td>1.021290</td>\n",
       "      <td>1.017914</td>\n",
       "      <td>0.930292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>1.040635</td>\n",
       "      <td>1.025498</td>\n",
       "      <td>1.036889</td>\n",
       "      <td>1.016040</td>\n",
       "      <td>0.764707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>1.044518</td>\n",
       "      <td>1.029298</td>\n",
       "      <td>1.041566</td>\n",
       "      <td>1.029931</td>\n",
       "      <td>0.747830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>1.053579</td>\n",
       "      <td>1.040313</td>\n",
       "      <td>1.049451</td>\n",
       "      <td>1.037813</td>\n",
       "      <td>0.991341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-09</th>\n",
       "      <td>1.062022</td>\n",
       "      <td>1.039762</td>\n",
       "      <td>1.053788</td>\n",
       "      <td>1.035553</td>\n",
       "      <td>0.682741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-10</th>\n",
       "      <td>1.052116</td>\n",
       "      <td>1.034751</td>\n",
       "      <td>1.045508</td>\n",
       "      <td>1.035387</td>\n",
       "      <td>0.580099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-11</th>\n",
       "      <td>1.060333</td>\n",
       "      <td>1.037559</td>\n",
       "      <td>1.055365</td>\n",
       "      <td>1.035002</td>\n",
       "      <td>0.528241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-12</th>\n",
       "      <td>1.002139</td>\n",
       "      <td>0.999449</td>\n",
       "      <td>0.999155</td>\n",
       "      <td>0.988700</td>\n",
       "      <td>4.272351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-16</th>\n",
       "      <td>1.021499</td>\n",
       "      <td>1.000936</td>\n",
       "      <td>1.002760</td>\n",
       "      <td>0.983298</td>\n",
       "      <td>1.993391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       open      high       low     close    volume\n",
       "ticker date                                                        \n",
       "FB     2018-01-02  1.000000  1.000000  1.000000  1.000000  1.000000\n",
       "       2018-01-03  1.023638  1.017623  1.021290  1.017914  0.930292\n",
       "       2018-01-04  1.040635  1.025498  1.036889  1.016040  0.764707\n",
       "       2018-01-05  1.044518  1.029298  1.041566  1.029931  0.747830\n",
       "       2018-01-08  1.053579  1.040313  1.049451  1.037813  0.991341\n",
       "       2018-01-09  1.062022  1.039762  1.053788  1.035553  0.682741\n",
       "       2018-01-10  1.052116  1.034751  1.045508  1.035387  0.580099\n",
       "       2018-01-11  1.060333  1.037559  1.055365  1.035002  0.528241\n",
       "       2018-01-12  1.002139  0.999449  0.999155  0.988700  4.272351\n",
       "       2018-01-16  1.021499  1.000936  1.002760  0.983298  1.993391"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faang = faang.reset_index().set_index(['ticker', 'date'])\n",
    "(faang / faang.groupby(level=['ticker']).transform('first')).head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
